Using the LiveDataGrid Control

The LiveDataGrid is designed as a remote-scripted replacement for the standard DataGrid. All of the functions supported by the standard DataGrid (such as selecting, editing, deleting, paging, and sorting) are supported by the LiveDataGrid. However, standard postbacks are replaced with "live" callbacks, enabling all of these functions to occur without any UI disruption, or browser "flash."

Follow the steps outlined in Using LiveControls so that a LiveGrid and a LiveLabel are added to the form.
Add some columns to the LiveDataGrid.
Note: For the purposes of this demo, the well-known "Northwind" database will be used. The LiveDataGrid will display data from the Products table.

The easiest way to add columns is to use the Property Builder. To do this, access the Design view in VS.NET. Under the Property Window, click the Property Builder ("Property Pages") link to open the editor.
Once the editor is opened, uncheck the "Create columns automatically" option.

Select BoundColumn in the Available Columns list. Click the right arrow button to add it to the Selected Column list. In the DataField textbox, add the database field name of the database to display. For the purposes of this tutorial, this will be the "ProductID" field. In the SortExpression field, type "ProductID" again. This field will be used for sorting data which is addressed later in the walkthrough. Now repeat the process for the "ProductName", "QuantityPerUnit", and "UnitsInStock" fields.
Add some button columns to the LiveDataGrid. Using the same editor, add a "Select", "Edit,Update,Cancel", and "Delete" column from under the "Button" node. Now the column adding process is complete.
Now use the Properties Window to set default properties for the LiveDataGrid. The properties of interest are the following:
- AllowPaging = "true"
- AllowSorting = "true"
- SelectedItemStyle = (choose your favorite style configuration)
Now "wire-up" the LiveDataGrid events that will be used. In C# this is easiest done by double clicking the event name in the Properties Window in design view. In VB.NET this is easiest done by selecting the proper events from the drop downs at the top of the code view. The events to wire up are:
- CancelCommand event
- DeleteCommand event
- EditCommand event
- PageIndexChanged event
- SortCommand event
- UpdateCommand event
Prepare the data source for the grid. Copy the Northwind.mdb database from the PowerWEB Samples installation folder to your WebForm's directory. Be sure that the database is not read-only and the ASPNET process has proper access rights to the database.
Now all of the general setup work is done, it is time to write the code required to perform the database tasks. There are several coding tasks that need to be done:
- Prepare data connection.
- Display data.
- Handle "edit".
- Handle "cancel".
- Handle "update".
- Handle "delete".
- Handle "sort".
- Handle "page".
First, import the Dart.LiveControls and System.Data.OleDb namespaces (the latter is required to connect to and use Access db files).
```
[C#]
using System.Data.OleDb;
using Dart.LiveControls;

[Visual Basic]
Imports System.Data.OleDb
Imports Dart.LiveControls
```

In the first load of the Page_Load event, prepare the connection and bind the data to the LiveDataGrid using a SELECT command. For the purposes of this tutorial, a DoBind function is added to perform the binding. This function can be reused by other events.

Also, create a Session variable "sortField" to track which column has been sorted.

[C#]
private void Page_Load(object sender, System.EventArgs e)
{
        if (!Page.IsPostBack)
        {
                DoBind("", "");
                Session["sortField"] = "ProductID";
        }
}

private void DoBind(string sortField, string sortDirection)
{
   string connectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("") + "\\Northwind.mdb";
   OleDbConnection connection = new OleDbConnection(connectionString);
   string sql = "SELECT * FROM Products";
   if(sortField != "")
      sql+= " ORDER BY " + sortField;
   if(sortField != "" sortDirection != "")
      sql+= " " + sortDirection;
   OleDbDataAdapter adapter = new OleDbDataAdapter(sql, connection);

   DataSet dataSet = new DataSet("DataSetProducts");
   adapter.Fill(dataSet, "Products");
                        
   DataView view = dataSet.Tables["Products"].DefaultView;
   LiveDataGrid1.DataSource = view;
   LiveDataGrid1.DataBind();

   Session["myData"] = view;
}

[Visual Basic]
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
   If Not IsPostBack Then
      DoBind("", "")
      Session("sortField") = "ProductID"
   End If
End Sub

Private Sub DoBind(ByVal SortField As String, ByVal SortDirection As String)
        Dim connectionString As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("") + "\Northwind.mdb"
        Dim connection As New OleDbConnection(connectionString)
        Dim sql As String = "SELECT * FROM Products"
        If (SortField <> "") Then sql += " ORDER BY " + SortField
        If (SortField <> "" And SortDirection <> "") Then sql += " " + SortDirection
        Dim adapter As New OleDbDataAdapter(sql, connection)
        Dim dataSet As New System.Data.DataSet("DataSetProducts")
        adapter.Fill(dataSet, "Products")
        Dim view As System.Data.DataView = dataSet.Tables("Products").DefaultView
        LiveDataGrid1.DataSource = view
        LiveDataGrid1.DataBind()

        Session("myData") = view
End Sub

Clicking the "edit" link on the "Edit, Update, Cancel" column raises the EditCommand event. The only task that needs to be performed here is to set the LiveDataGrid.EditItemIndex. This should look like the following:

[C#]
private void LiveDataGrid1_EditCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
   LiveDataGrid1.EditItemIndex = e.Item.ItemIndex;
   LiveDataGrid1.DataSource = (DataView)Session["myData"];
   LiveDataGrid1.DataBind();
}

[Visual Basic]
Private Sub LiveDataGrid1_EditCommand(ByVal sender As System.Object, ByVal e As DataGridCommandEventArgs) Handles LiveDataGrid1.EditCommand
   LiveDataGrid1.EditItemIndex = e.Item.ItemIndex
   LiveDataGrid1.DataSource = Session("myData")
   LiveDataGrid1.DataBind()
End Sub

Clicking "edit" will change all fields for the selected row to TextBox controls, allowing those fields to be edited. The "Edit" link will change to an "Update" and "Cancel" link, enabling the user to either save their changes or cancel editing. The events associated with these actions are the UpdateCommand and CancelCommand events. Write the code required to handle these events.

In the CancelCommand, reset LiveDataGrid.EditItemIndex to -1 and rebind.

In the UpdateCommand event, add database code to update the fields. For this, a DoCommand function is added which can also be reused by the Sort command.

[C#]
private void DoCommand(string cmdText)
{
   string connectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("") + "\\Northwind.mdb";
   OleDbConnection connection = new OleDbConnection(connectionString);
   connection.Open();
   OleDbCommand command = new OleDbCommand(cmdText, connection);
   command.ExecuteNonQuery();
   connection.Close();
}

private void LiveDataGrid1_CancelCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
   LiveDataGrid1.EditItemIndex = -1;
   DoBind(Session["sortField"].ToString(), "");
}

private void LiveDataGrid1_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
   // Get the new values from the controls
   string key = ((LiveTextBox)e.Item.Cells[0].Controls[0]).Text;
   string prodName = ((LiveTextBox)e.Item.Cells[1].Controls[0]).Text;
   string quantity = ((LiveTextBox)e.Item.Cells[2].Controls[0]).Text;
   string units = ((LiveTextBox)e.Item.Cells[3].Controls[0]).Text;

   // Run the query
   DoCommand("UPDATE Products Set ProductName = '" + prodName + "', QuantityPerUnit = '" + quantity + "', UnitsInStock = '" + units + "' WHERE ProductID = " + key);

   // Reset the EditItemIndex
   LiveDataGrid1.EditItemIndex = -1;

   // Rebind the grid to display the new values
   DoBind(Session["sortField"].ToString(), "");
}

[Visual Basic]
Private Sub DoCommand(ByVal CmdText as String)
   Dim connectionString as String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("") + "\Northwind.mdb"
   Dim connection as new OleDbConnection(connectionString)
   connection.Open()
   Dim command as New OleDbCommand(CmdText, connection)
   command.ExecuteNonQuery()
   connection.Close()
End Sub

Private Sub LiveDataGrid1_CancelCommand(ByVal sender As System.Object, ByVal e As DataGridCommandEventArgs) Handles LiveDataGrid1.CancelCommand
   LiveDataGrid1.EditItemIndex = -1
   DoBind(Session("sortField").ToString(), "")
End Sub

Private Sub LiveDataGrid1_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles LiveDataGrid1.UpdateCommand
        ' Get the new values from the controls
        Dim ltb As LiveTextBox = e.Item.Cells(0).Controls(0)
        Dim key As String = ltb.Text
        Dim box As LiveTextBox = e.Item.Cells(1).Controls(0)
        Dim prodName As String = box.Text
        box = e.Item.Cells(2).Controls(0)
        Dim quantity As String = box.Text
        box = e.Item.Cells(3).Controls(0)
        Dim units As String = box.Text

        ' Run the query
        DoCommand("UPDATE Products Set ProductName = '" + prodName + "', QuantityPerUnit = '" + quantity + "', UnitsInStock = '" + units + "' WHERE ProductID = " + key)

        ' Reset the EditItemIndex
        LiveDataGrid1.EditItemIndex = -1

        ' Rebind the grid to display the new values
        DoBind(Session("sortField").ToString(), "")
End Sub

For the PageIndexChangedCommand event, the LiveDataGrid.CurrentPageIndex is updated and the data is re-bound.

[C#]
private void LiveDataGrid1_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{  
   LiveDataGrid1.CurrentPageIndex = e.NewPageIndex;
   DoBind(Session["sortField"].ToString(), "");
}

[Visual Basic]
Private Sub LiveDataGrid1_PageIndexChanged(ByVal sender As System.Object, ByVal e As DataGridPageChangedEventArgs) Handles LiveDataGrid1.PageIndexChanged
   LiveDataGrid1.CurrentPageIndex = e.NewPageIndex
   DoBind(Session("sortField").ToString(), "")
End Sub

For the SortCommand event, store the "sortField" column and rebind the data using the SortExpression specified when the column was prepared earlier in the tutorial. Also, recall the DoBind function permits a direction to be specified ("asc" or "desc", depending on which way the data is to be sorted). For the purposes of this tutorial, "asc" will be hard-coded.

[C#]
private void LiveDataGrid1_SortCommand(object source, System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
{
        Session["sortField"] = e.SortExpression;
        DoBind(e.SortExpression, "asc");
}

[Visual Basic]
Private Sub LiveDataGrid1_SortCommand(ByVal sender As System.Object, ByVal e As DataGridSortCommandEventArgs) Handles LiveDataGrid1.SortCommand
        Session("sortField") = e.SortExpression 
        DoBind(e.SortExpression, "asc")
End Sub

For the Delete column, add code to send a DELETE command to the database and rebind.

 [C#]
protected void LiveDataGrid1_DeleteCommand(object source, DataGridCommandEventArgs e)
{
        string key = e.Item.Cells[0].Text;
        DoCommand("DELETE * FROM Products WHERE ProductID = " + key);
        DoBind(Session["sortField"].ToString(), "");
}

[Visual Basic]
Private Sub LiveDataGrid1_DeleteCommand(ByVal sender As System.Object, ByVal e As DataGridCommandEventArgs) Handles LiveDataGrid1.DeleteCommand
        Dim key as String = e.Item.Cells(0).Text
        DoCommand("DELETE * FROM Products WHERE ProductID = " + key)
        DoBind(Session("sortField").ToString(), "")
End Sub

For the Select column in this simple demonstration, add code to the SelectedIndexChanged event to display the selected Product Name in the LiveLabel added earlier.

 [C#]
protected void LiveDataGrid1_SelectedIndexChanged(object sender, EventArgs e)
{
        LiveLabel1.Text = LiveDataGrid1.Items[LiveDataGrid1.SelectedIndex].Cells[1].Text; 
}

[Visual Basic]
Private Sub LiveDataGrid1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As EventArgs) Handles LiveDataGrid1.SelectedIndexChanged
        LiveLabel1.Text = LiveDataGrid1.Items(LiveDataGrid1.SelectedIndex).Cells(1).Text 
End Sub

Compile and run the Web application. You should now see a datagrid containing data from the "Northwind" database. Try some of the functions that were implemented, such as selecting rows, changing data, sorting, and paging.